from shoeKeyWords import *
from notneeded import *
from spokeMen import *
from brands import *

import numpy as np
import datetime
import random
import xlrd
import math
import re

# 获取订单明细列表
def getOrderList(path, titles):
    ordersDict = {}

    orderData = xlrd.open_workbook(path)
    orderTable = orderData.sheet_by_index(0)

    for row in range(1, orderTable.nrows):
        for t in titles:
            title = orderTable.cell_value(row, 19)
            reason = orderTable.cell_value(row, 27)

            if not hasCommonModelNum(title, t[0]):
                continue

            if not t[0] in ordersDict:
                ordersDict[t[0]] = {}

            # 以orderId为key，以orderTime，actualPay为被映射的对象
            orderId, orderTime, actualPay = orderTable.cell_value(row, 0), orderTable.cell_value(row, 17), orderTable.cell_value(row, 8)
            ordersDict[t[0]][orderId] = {
                "orderTime": xlrd.xldate.xldate_as_datetime(orderTime, orderData.datemode),
                "actualPay": actualPay,
                "reason": reason
            }

    return ordersDict

# 清洗订单数据，形成一个规范的、可供分析数据集,ExportOrderDetailList.xlsx
def getDataList(table, nrows, ncols, orderDict, titles):
    datalist = {}

    for row in range(1, nrows):
        for t in titles:
            line = []

            id = table.cell_value(row, 0)
            title = table.cell_value(row, 1)
            state = table.cell_value(row, 8)

            if not hasCommonModelNum(title, t[0]):
                continue

            if not id in orderDict[t[0]]:
                continue

            if state == '未创建支付宝交易' or state == '等待买家付款':
                continue

            # 交易关闭的原因有好几种，只有原因为退款的交易关闭的宝贝参与运算
            if state == '交易关闭' and orderDict[t[0]][id]['reason'] != '退款':
                continue

            # 数字标题废弃
            if isinstance(title, float) or isinstance(title, int):
                continue

            # 空标题废弃
            if len(title) < 3:
                continue

            # 运费和邮费补差价废弃
            if title.find('运费') != -1 or title.find('邮费') != -1:
                continue

            # 如果是不需要的运算的宝贝则跳过
            if isNotNeeded(title):
                continue

            # 把每一个属性都存入数组
            for k in range(ncols):
                line.append(table.cell_value(row, k))

            # 品牌
            brand = getBrand(title)
            if brand != None:
                line.append(brand)
            else:
                line.append(None)

            # 代言人
            spokeMan = getSpokeMan(title)
            if spokeMan != None:
                line.append(spokeMan)
            else:
                line.append(None)

            # 宝贝品类
            category = getCategory(title)
            if category != None:
                line.append(category)
            else:
                line.append(None)

            if category == None:
                continue

            # 实际支付金额
            if id in orderDict[t[0]]:
                line.append(orderDict[t[0]][id]["actualPay"])
            else:
                print(title, 'has no actualPay')
                line.append(None)

            # 订单时间
            if id in orderDict[t[0]]:
                line.append(orderDict[t[0]][id]["orderTime"])
            else:
                line.append(None)

            if orderDict[t[0]][id]["orderTime"] == None:
                continue

            if not t[0] in datalist:
                datalist[t[0]] = []

            datalist[t[0]].append(line)

    # print('实际有效数据{0}行'.format(len(datalist)))
    return datalist

# 汇总宝贝数据
def summaryProducts(datalist, titles):
    productsSummary = {}

    for t in titles:
        productsSummary[t[0]] = {
            'data': [],
            'saleAmt': 0,
            'saleMoneny': 0,
            'brand': getBrand(t[0]),
            'category': getCategory(t[0]),
            'spkoeMan': getSpokeMan(t[0])
        }

        for row in range(0, len(datalist[t[0]])):
            count = int(datalist[t[0]][row][3])
            price = float(datalist[t[0]][row][2])

            productsSummary[t[0]]['saleAmt'] += count
            productsSummary[t[0]]['saleMoneny'] += count * price
            productsSummary[t[0]]['data'].append(datalist[t[0]][row])

            # for k in productsSummary:
            #     # 如果是完全相同的标题或者是有至少一个共同的款号同时有相同的品牌和品类，则认为这两个宝贝是同一个宝贝
            #     if (title == k) or hasCommonModelNum(title, k):
            #         productsSummary[k]['saleAmt'] += count
            #         productsSummary[k]['saleMoneny'] += count * price
            #         productsSummary[k]['data'].append(datalist[row])
            #
            #         matched = True
            #         break

            # if not matched:
            #     productsSummary[title] = {
            #         'data': [],
            #         'saleAmt': 0,
            #         'saleMoneny': 0,
            #         'brand': getBrand(title),
            #         'category': getCategory(title),
            #         'spkoeMan': getSpokeMan(title)
            #     }
            #
            #     productsSummary[title]['saleAmt'] += count
            #     productsSummary[title]['saleMoneny'] += count * price
            #     productsSummary[title]['data'].append(datalist[row])

    return productsSummary

# 搜索某款宝贝
def getProduct(productsSummary, title):
    for t in productsSummary:
        if hasCommonModelNum(title, t):
            return productsSummary[t]["data"]

    return None

# 获取类目
def getCategory(title):
    for k in runningShoeKeyWords:
        if title.find(k) != -1:
            return '跑步鞋'

    for k in casualShoeKeyWords:
        if title.find(k) != -1:
            return '休闲鞋'

    for k in baskballShoeKeyWords:
        if isinstance(k, list):
            flag = True
            for n in k:
                if title.find(n) == -1:
                    flag = False
            if flag:
                return '篮球鞋'
        elif title.find(k) != -1:
            return '篮球鞋'

    for k in skateShoeKeyWords:
        if title.find(k) != -1:
            return '板鞋'

    return None

# 获取品牌
def getBrand(title):
    for brand in brands:
        if title.find(brand) != -1:
            return brands[brand]

    return None

# 获取代言明星
def getSpokeMan(title):
    for i in range(len(spokeMen)):
        if title.find(spokeMen[i]) != -1:
            if spokeMen[i] in spokeManDict:
                return spokeManDict[spokeMen[i]]
            return spokeMen[i]

    return None

# 判断是否是不需要运算的宝贝
def isNotNeeded(title):
    for i in range(len(notneeded)):
        if title.find(notneeded[i]) != -1:
            return True

    return False

# 获取宝贝所有款号
def getModelNums(title):
    result = re.compile('[A-Z]*[0-9]{4,}').findall(title)

    if len(result):
        for i in range(len(result)):
            if result[i].find('2015') != -1 or result[i].find('2016') != -1 or result[i].find('2017') != -1 or result[
                i].find('2019') != -1:
                result[i] = ""

        return result

    return []

# 判断两个宝贝是否有共同款号
def hasCommonModelNum(t0, t1):
    modelNums0, modelNums1 = getModelNums(t0), getModelNums(t1)
    # print(t0, modelNums0, t1, modelNums1)
    if len(modelNums0) == 0 or len(modelNums1) == 0:
        return False

    for v0 in modelNums0:
        for v1 in modelNums1:
            if v0 != '' and v1 != '' and v0 == v1:
                return True

    return False

# 计算某款宝贝最早卖出的时间到最近卖出时间差值
def firDaySaleToLastDaySale(products):
    firstDay = products[0][-1]
    lastDay = products[0][-1]

    for d in products:
        if (d[-1] - firstDay).days < 0:
            firstDay = d[-1]

        if (d[-1] - lastDay).days > 0:
            lastDay = d[-1]

    return (lastDay - firstDay).days

def firstDaySale(products):
    firstDay = products[0][-1]

    for d in products:
        if (d[-1] - firstDay).days < 0:
            firstDay = d[-1]

    return firstDay

# 计算平均价格
def countAvgPrice(products):
    total = 0

    for p in products:
        total += int(p[2])

    return total / len(products)

# 判断两个价格是否都在同一个区间(0,300)/(300,600)/(600,1000),(1000,)
def samePriceGrade(p0, p1):
    p0 = int(p0)
    p1 = int(p1)

    if (p0 <= 300 and p1 <= 300) or (p0 > 300 and p0 <= 600 and p1 > 300 and p1 <= 600) or (
                            p0 > 600 and p0 <= 1000 and p1 > 600 and p1 <= 1000) or (p0 > 1000 and p1 > 1000):
        return True

    return False

# 获取同类宝贝
def getSimilarProducts(price, brand, category, spokeMan, title):
    result = {}

    for t in productsSummary:
        # 如果遍历到同款宝贝，则跳过，不予计算
        # if hasCommonModelNum(t, title):
        #     continue

        if brand != productsSummary[t]['brand']:
            continue

        if category != productsSummary[t]['category']:
            continue

        if spokeMan != productsSummary[t]['spkoeMan']:
            continue

        # 如果title中没有说明是女鞋，而t中说明是女鞋的情况下，过滤此条
        if title.find('女') == -1 and t.find('女') != -1:
            continue

        # 计算同类宝贝的平均价格和此款宝贝的价格,判断它们是否都在同一个区间
        avgPrice = countAvgPrice(productsSummary[t]['data'])
        if not samePriceGrade(price, avgPrice):
            continue

        result[t] = productsSummary[t].copy()

    return result

# 获取鞋码
def getSize(info):
    return info.split('：')[-1].split(' ')[0]

# 解析款号（去除中文字符）
def getModelInModelAndSize(modelAndSize):
    if len(modelAndSize) == 0:
        return ""
    # print(modelAndSize,modelAndSize.split(';')[0],modelAndSize.split(';')[0].split('：')[1])
    model = modelAndSize.split(';')[0].split('：')[1]

    if len(re.compile('[A-Za-z/0-9\-]*').findall(model)):
        model = re.compile('[A-Za-z/0-9\-]*').findall(model)[0]

    return model

# 为库存打分，综合考虑件数和尺码丰富度
def scoreForStock(stock):
    result = 0
    for size, count in stock.items():
        result += 1
        # if count <= 30:
        #     result += count * countSizeWeight(size)
        # else:
        #     result += 30 * countSizeWeight(size)
        result += count * countSizeWeight(size)

    return round(result, 2)

def countToalSale(sales):
    result = 0
    for size, count in sales.items():
        result += int(count)

    return result

def countTotalSaleByWeek(skuDataInWeek):
    """
    skuDataInWeek is array
    :param skuDataInWeek:
    :return:
    """

    result = {}

    for v in skuDataInWeek:
        for size, count in v["sale"].items():
            if not size in result:
                result[size] = 0
            result[size] += count

    return {k: v for k, v in sorted(result.items())}

def countTotalStockByWeek(totalSaleByWeek, nextWeekStartStock):
    result = {}

    for size, count in totalSaleByWeek.items():
        if not size in result:
            result[size] = 0

        if size in nextWeekStartStock["stock"]:
            result[size] = count + nextWeekStartStock["stock"][size]
        else:
            result[size] = count

    # 遍历出那些没有销售出去但是有剩余库存的尺码
    for size, count in nextWeekStartStock["stock"].items():
        if not size in result:
            result[size] = count

    return result

# 计算不同尺码的权重
info = {
    "43": 9437,
    "42.5": 8987,
    "42": 8338,
    "41": 6610,
    "44": 6367,
    "44.5": 2947,
    "40.5": 2644,
    "45": 2267,
    "40": 1639,
    "38": 1100,
    "36.5": 799,
    "37.5": 726,
    "39": 715,
    "46": 666,
    "38.5": 660,
    "36": 585,
    "37": 331,
    "35.5": 266,
    "45.5": 43,
    "47": 25,
    "47.5": 23,
    "41.5": 19,
    "46.5": 19,
    "39.5": 10,
    "48.5": 7,
    "48": 5,
    "43.5": 5,
    "35": 3
}

high_hot_sizes = ['43', '42.5', '42', '41', '44']
middle_hot_sizes = ['44.5', '40.5', '45', '40']

# total = 0
#
def countSizeWeight(size):
    if size in info:
        return round(info[size] / 20000, 2)
    else:
        return 0.01

# 获得某个尺码的热销程度
def sizeHotSale(size):
    if info[size] > 8000:
        return 3
    elif info[size] > 1000:
        return 2
    else:
        return 1

# 所有历史销售数据都为空
def emptyHistorySale(historySkuDataInWeeks):
    # if len(historySkuDataInWeeks) < 2:
    #     return False

    for item in historySkuDataInWeeks:
        if len(item['sale']):
            return False

    return True

# 根据尺寸获取历史销售数据数组
def saleArrBySize(historySkuDataInWeeks, size_):
    arr = []

    for week in historySkuDataInWeeks:
        for size, count in week['sale'].items():
            if size == size_:
                arr.append(count)

    return arr

# 根据尺寸获取历史库存数据数组
def stockArrBySize(historySkuDataInWeeks, size_):
    arr = []

    for week in historySkuDataInWeeks:
        for size, count in week['stock'].items():
            if size == size_:
                arr.append(count)

    return arr

# 剔除数组中的异常值
def eliminateAbnormalValue(arr):
    if len(arr) == 0:
        return []

    mean = sum(arr) / len(arr)

    result = []

    if len(arr) == 1:
        return arr
    elif len(arr) == 2:
        if min(arr) == 1 and max(arr) >= 8:
            result.append(min(arr))
        elif max(arr) / min(arr) > 4:
            result.append(min(arr))
        else:
            return arr
    else:
        for v in arr:
            if v > mean * 2:
                continue

            result.append(v)

    return result

def avg(arr):
    return sum(arr) / len(arr)

def increasing(s):
    # for i in range(len(arr) - 1):
    #     if arr[i + 1] < arr[i]:
    #         return False
    # return True
    result = [s[idx + 1] - s[idx] for idx in range(len(s) - 1)]

    for v in result:
        if v < 0:
            return False

    return result

# 统计截止到某个日期之前的尺码销量数据
def countSizeSaleEndByDate(itemList, model_, endDate):
    endDate = endDate + datetime.timedelta(days = 1)

    result = {}

    for item in itemList:
        if len(item[5]) == 0:
            continue

        model, size, date = item[5].split(';')[0].split('：')[1], item[5].split(';')[1].split('：')[1], item[-1]

        if (date - endDate).days >= 0 or model != model_:
            continue;

        if not size in result:
            result[size] = 0

        result[size] += int(item[3])

    return result

# 从历史统计来看和某个目标size最接近的size
def nearestSize(sizeSale, size):
    info = sorted(sizeSale.items(), key = lambda d: d[1], reverse = True)

    for idx, val in enumerate(info):
        if val[0] == size:
            if idx == 0:
                return info[1][0]
            elif idx == len(info) - 1:
                return info[-2][0]
            else:
                return (info[idx - 1][0], info[idx + 1][0])
    return ()

actualSales = 0
predictSales = 0
avgError = []

# 改进之后的sku销售预测
def predictSkuSaleCount(historySkuDataInWeeks, predictedSkuStock, actualSkuSale, model, sizeSale):
    global actualSales, predictSales

    result = {}

    lastWeekData = historySkuDataInWeeks[-1]

    # 如果历史上一直销量为零，那么预测后面的周期也为零
    if emptyHistorySale(historySkuDataInWeeks):
        for size in predictedSkuStock:
            # 如果是特惠处理，那么基本上每个尺寸都能卖出一双
            if model.find('特惠') != -1 or model.find('处理') != -1:
                result[size] = 1
            else:
                result[size] = 0

        return result

    # 只有一周的历史销售数据
    if len(historySkuDataInWeeks) == 1:
        for size, count in predictedSkuStock.items():
            # 如果上周有销量
            if size in lastWeekData['sale']:
                if lastWeekData['sale'][size] >= 3:
                    result[size] = count

                elif lastWeekData['sale'][size] == 1:
                    result[size] = 1

                else:
                    result[size] = lastWeekData['sale'][size]

            # 如果上周没有销量
            else:
                # 如果历史上有过销量
                if size in sizeSale:
                    result[size] = 1
                else:
                    result[size] = 0

    # 有一周以上的历史销售数据
    elif len(historySkuDataInWeeks) > 1:
        for size, count in predictedSkuStock.items():
            # 获取历史销售数据并剔除一些极端值
            sales = saleArrBySize(historySkuDataInWeeks, size)
            stocks = stockArrBySize(historySkuDataInWeeks, size)

            # 如果在可供分析的周期内这个尺码是第一次上架，那么根据相近原则选择历史总销相近的尺码作为预测依据
            if len(stocks) == 0:
                _ = []

                for s in nearestSize(sizeSale, size):
                    if s in lastWeekData['sale']:
                        _.append(lastWeekData['sale'][s])

                result[size] = int(avg(_) if len(_) else 0)
                continue

            # 如果有一个以上的历史数据,暂时简单地取一个均值
            if len(sales) > 1:
                # 如果最近一周没有销量则预测为零
                if not size in lastWeekData['sale']:
                    result[size] = 0
                else:
                    result[size] = round(avg(sales))

            # 如果只有一个周期有过销量
            elif len(sales) == 1:
                # 如果最近一周这个尺码没有销量
                if not size in lastWeekData['sale']:
                    # 如果这个尺码历史总销低于10件那么预测为0，否则预测为1件
                    if sizeSale[size] < 5:
                        result[size] = 0
                    else:
                        result[size] = 1

                # 如果最近一周有销量
                if size in lastWeekData['sale']:
                    result[size] = sales[0]

            # 如果没有历史周期数据可供参考
            else:
                # 如果历史上有过销量
                if size in sizeSale:
                    result[size] = 1
                else:
                    result[size] = 0

    # 整理一下预测值
    for size in result:
        # 库存数据
        count = predictedSkuStock[size]

        result[size] = result[size] if result[size] >= 0 else 0
        result[size] = result[size] if result[size] <= count else count

    return result

def report():
    print('预测总计:', predictSales)
    print('实际总计:', actualSales)
    print('误差集合:', avgError)
    # print('平均误差', avg(avgError))
    # print('准确率:', 1 - abs(predictSales - actualSales) / predictSales)
